Dave Ladouceur owns a catering company that prepares banquets and parties for both individual and business functions throughout the year. Mr. Ladouceur’s business is seasonal, with a heavy schedule during the summer months and the year-end holidays and a light schedule at other times. During peak periods there are extra costs. He shuts down in January to repair and maintain and clean.
One of the major events Mr. Ladouceur’s customers request is a cocktail party. He offers a standard cocktail party and has developed the following cost structure on a per-person basis.
Mr. Ladouceur is quite certain about his estimates of the food, beverages, and labour costs but is not as comfortable with the overhead estimate. This estimate was based on the actual data for the past 12 months, presented below. These data indicate that overhead expenses vary with the direct labour-hours expended. The $15.70-per-hour estimate was determined by dividing total overhead expended for the 12 months by total labour-hours.
Mr. Ladouceur has recently become aware of regression analysis. He estimated the following regression equation with overhead costs as the dependent variable (y) and labour- hours as the independent variable (X):
y = $31,886 + $9.45X + e
REQUIRED
1. Using Excel and the data provided in the table, complete a regression analysis at a confidence level of 95%.
2. What important information is presented in the r2, t-Stat, and P-values?
3. What is the range within which Mr. Ladouceur can be confident of the values of a and b? 4. Mr. Ladouceur:has- been asked to prepare a bid for a 200-person cocktail party to be given next month. Determine the minimum bid price that Mr. Ladouceur would be willing to submit to earn a positive contribution margin using his estimate and the results of the linear regression analysis. Explain Mr. Ladouceur’s problem with using the linear regression results.
5. What further information does the chart of the residuals provide?
6. Months Labour-Hours Overhead Costs
SOLUTION
1. On the left is the chart of the predicted (y), the linear regression line. On the right is the summary of relevant statistics extracted and reformatted from the Excel report.
2. The RSquare (r2) indicated that a change in labour hours will explain approximately 73% of a change in the total overhead cost pool. This is higher than the benchmark of 30% explanatory power. The t-Stat indicates whether or not the intercept a value is random or not and in this case 3.5044 > 2.202 at df = 11 and confidence level of 95%. The value of 2.202 is read from Exhibit 10-6 in the list of critical values in Appendix. Bob can be confident 95/100 times that the unexplained portion of change in the overhead cost pool is approximately $31,886.
The P-value of 0.0057 tells Bob that 57/1,000 times the actual unexplained value will not be within a reasonable range of $31,886. Similarly for the rate of change Bob can expect in the overhead cost pool when labour hours change by 1 unit is approximately b = $9.45/hour. Again the t-Stat of 5.2290 > 2.202 at df = 11 and confidence level of 95%. the P-value is only 4/1,000 that an observed rate of change will be beyond a reasonable range of $9.45.
3. Using the data provided in the regression output, the predicted range of values within which any future observed value of a and b should fall is calculated as:
Range: a (critical value * (standard error of a÷√n)
and: b (critical value * (standard error of b÷√n)
The results of these calculations are:
| RANGE OF COEFFICIENT VALUES |
|---|
| Cost Driver Labour Hours | Cost Driver Labour Hours | 95.0% | | | |
| | df = 11 | √n= | 3.317 | |
| Coefficients | Critical Value | Standard Error | High | Low |
| Intercept a | $31,886.03 | 2.202 | 9,098.884 | $37,863.93 | $24,908.12 |
| Slope b | $ 9.45 | 2.202 | 1.808 | $ 10.64 | $ 8.26 |
The highest estimated value of intercept a is approximately $37,864 and lowest is $25,908.
The highest estimated value of slope b is approximately $10.64 and lowest is $8.26.
4.
| Total Variable | a | Total Cost |
|---|
| Mr. Ladouceur’s figures, 200 people | $6,083.48 | | $6,083.48 |
| Using linear regression, 200 people | $2,079.47 | 31,886.03 | 33,965.50 |
To earn a positive contribution margin, the minimum bid for a 200-person cocktail party would be any amount greater than $6,084. This amount is calculated by multiplying the variable cost per person of $30.42 by the 200 people. At a price above the variable costs of $6,084, Mr. Ladouceur will be earning a contribution margin toward coverage of his fixed costs.
Of course, Mr. Ladouceur will consider other factors in developing his bid including (a) an analysis of the competition––vigorous competition will limit Ladouceur’s ability to obtain a higher price (b) a determination of whether or not his bid will set a precedent for lower prices––overall, the prices Mr. Ladouceur charges should generate enough contribution to cover fixed costs and earn a reasonable profit, and (c) a judgment of how representative past historical data (used in the regression analysis) is about future costs.
Using the data Mr. Ladouceur will overbid using the linear regression and will not obtain the job. He is better off to use his own estimate of costs based on his own data. There must be another cost driver that better specifies how overhead costs change systematically. The intercept value of a is not a fixed cost. It is the estimated dollar value of cost change that is not explained by a change in labour hours.
5. Mr. Ladouceur is making a mistake when he assumes that the value of a is a fixed cost. The MOH cost pool is heterogeneous. Some of the costs are fixed but some are variable. Of the variable costs not all are driven by labour hours, although Mr. Ladouceur has chosen this as his cost driver. Mr. Ladouceur only has 12 data points and the minimum he should have to derive a reliable systematic relationship is 31 data points. Mr. Ladouceur should not use the specification derived from the linear regression.
6. The chart of the residuals shows that the error terms e are not randomly scattered around the mean (the horizontal line). After the initial increase from January to February, there is a steady downward drift to the values. The residuals also appear to have a wave form. This is further evidence that a reliable cost driver has not yet been specified.